
DROP PROCEDURE IF EXISTS candidate_filter_skill;

DELIMITER //
CREATE PROCEDURE `candidate_filter_skill` (
    IN skill INT, IN skillImp INT)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER

BEGIN

    -- MAXIMUM SCORE IS 10 * (MAX Years of Experience) AND MINIMUM SCORE IS 0
    IF skillImp = 10 THEN
        SELECT 
            can.id AS col1, 
            CONCAT (can.last_name, ' ', can.middle_name, ' ', can.first_name) AS col2,
            can.email AS col3, 
            can.contact_number AS col4,
            10 * canSkill.years_of_exp AS col5
        FROM
            hrentry_db.hre_job_candidate AS can,
            hrentry_db.hre_candidate_skill AS canSkill
        WHERE
            can.is_deleted = 0
            AND canSkill.candidate_id = can.id
            AND canSkill.skill_id = skill;
    ELSE
        SELECT
            can.id AS col1, 
            CONCAT (can.last_name, ' ', can.middle_name, ' ', can.first_name) AS col2,
            can.email AS col3, 
            can.contact_number AS col4, 
            skillImp * canSkill.years_of_exp AS col5
        FROM
            hrentry_db.hre_job_candidate AS can,
            hrentry_db.hre_candidate_skill AS canSkill
        WHERE
            can.is_deleted = 0
            AND canSkill.candidate_id = can.id
            AND canSkill.skill_id = skill;
    END IF;
END//

